package com.ruoyi.tongji.run;

import com.ruoyi.tongji.entity.Staff;
import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.*;
import com.ruoyi.tongji.utils.DateUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 职工任务数统计
 */
public class 员工二次采浆统计 {

    public static Date dateRangeMonth = DateUtil.getDate("2024-05-01");
    public static String renyuanDateRangeStart = "202404010000000";
    public static String renyuanDateRangeEnd =   "2024043012359999";
    public static String renyuanDateRangeNextEnd =   "202405312359999";

    public static String root = "E:\\文档\\2024\\每月统计\\" + DateUtils.getMonth(dateRangeMonth) + "月\\";


    public static void main(String[] args) {
        SQLManager jiangzhanSqlManager = ManagerFactory.jiangzhanSqlManager();

        List<Staff> staffList = jiangzhanSqlManager.execute(new SQLReady("select * from member", new Object[] {}), Staff.class);

        Map model = new HashMap();
        model.put("staffList", staffList);
        model.put("dateRange",renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8));
        for (Iterator<Staff> iterator = staffList.iterator(); iterator.hasNext(); ) {
            Staff staff =  iterator.next();
            getStaff(staff);
        }


        System.out.println(model);









        String excelTemplate = "excelTemplates/员工/员工新浆员、超半年、检疫期（献浆后2个月内）.xlsx";
        try(InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(excelTemplate)) {
            if(is==null) {
                throw new PlatformException("模板资源不存在："+excelTemplate);
            }
            File f = new File(root + "\\程序查询\\员工新浆员、超半年、检疫期（献浆后2个月内）" +  renyuanDateRangeStart.substring(0,8) + "至" + renyuanDateRangeEnd.substring(0,8) + ".xlsx");
            f.getParentFile().mkdirs();
            FileOutputStream os = new FileOutputStream(f);
            JxlsUtils.exportExcel(is,os,model);
        } catch (IOException e) {
            throw new PlatformException(e.getMessage());
        }

    }


    public static Map getStaff(Staff staff) {
        SQLManager sqlManager = ManagerFactory.taibangSqlManager();
        //时间范围内超半年人员
        List<HashMap> chaobannianList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(lastCollectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donorName,donorId as aa,curColDate,\n" +
                "\t\t(select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as lastCollectionDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' \n" +
                "     and currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days > (180) and c.collectionCount > 0", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd, renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long chaobannian = (Long)chaobannianList.get(0).get("count(*)");
        staff.set("chaobannianTotal",chaobannian);

        //时间范围内超半年人员，2个月内采浆次数大于1次的
        List<HashMap> chaobanniandayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(lastCollectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donorName,donorId as aa,curColDate,\n" +
                "\t\t(select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as lastCollectionDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' \n" +
                "     and currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days > (180) and c.collectionCount > 2", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd, renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long chaobanniandayu1ci = (Long)chaobanniandayu1ciList.get(0).get("count(*)");
        staff.setChaoCount(chaobanniandayu1ci.intValue());


        //时间范围内检疫期人员，2个月内采浆次数
        List<HashMap> jianyiqiList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(lastCollectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donorName,donorId as aa,curColDate,\n" +
                "\t\t(select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as lastCollectionDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' \n" +
                "     and currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days >= (90) and c.days < (180)  and c.collectionCount > 0", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd, renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long jianyiqiCount = (Long)jianyiqiList.get(0).get("count(*)");
        staff.set("jianyiqiTotal",jianyiqiCount);

        //时间范围内检疫期人员，2个月内采浆次数
        List<HashMap> jianyiqidayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select *,(TO_DAYS(DATE_FORMAT(left(curColDate,8),'%Y%m%d'))-TO_DAYS(DATE_FORMAT(left(lastCollectionDate,8),'%Y%m%d'))) as days from (\n" +
                "\tselect donorName,donorId as aa,curColDate,\n" +
                "\t\t(select c.beginDate from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate < curColDate and c.weight > 0 and c.collectionNumberFlag = 'Y' order by c.beginDate desc limit 0,1 ) as lastCollectionDate,\n" +
                "\t\t(select count(*) from t_bims_plasma_collection as c where c.donorId = aa and c.beginDate between ? and ? and c.weight > 0 order by c.beginDate desc ) as collectionCount\n" +
                "\tfrom (\n" +
                "\t   select donor.donorName as donorName,donor.donorId as donorId, currentCollect.beginDate as curColDate from t_bims_plasma_collection as currentCollect left join t_bims_donor as donor on currentCollect.donorId = donor.donorId where currentCollect.collectionNumberFlag = 'Y' \n" +
                "     and currentCollect.beginDate between ? and ? and donor.remark like ? \n" +
                "\t) as a\n" +
                ") as b) as c where c.days >= (90+28) and c.days < (180)  and c.collectionCount > 2", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd, renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long jianyiqidayu1ciCount = (Long)jianyiqidayu1ciList.get(0).get("count(*)");
        staff.setJianXianCount(jianyiqidayu1ciCount.intValue());


        //时间范围内新浆员，2个月内采浆次数
        List<HashMap> xinList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select donor.donorId as dId, left(donor.firstCollectDate,8) as startDate,date_add(left(donor.firstCollectDate,8), interval 60 day) as endDate,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                " from (\n" +
                "\tselect * from t_bims_donor as donor where donor.firstCollectDate between ? and ? and remark like ?\n" +
                ") as donor\n" +
                ") as a order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long xinCount = (Long)xinList.get(0).get("count(*)");
        staff.set("xinTotal",xinCount.intValue());

        //时间范围内新浆员，2个月内采浆次数
        List<HashMap> xindayu1ciList = sqlManager.execute(new SQLReady("select count(*) from (\n" +
                "select donor.donorId as dId, left(donor.firstCollectDate,8) as startDate,date_add(left(donor.firstCollectDate,8), interval 60 day) as endDate,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between ? and ? and c.weight > 0 and c.collectionNumberFlag = 'Y' ) as cCount\n" +
                " from (\n" +
                "\tselect * from t_bims_donor as donor where donor.firstCollectDate between ? and ? and remark like ?\n" +
                ") as donor\n" +
                ") as a where a.cCount > 2 order by a.cCount", new Object[] { renyuanDateRangeStart, renyuanDateRangeNextEnd,renyuanDateRangeStart, renyuanDateRangeEnd,"%" + staff.getName() + "%"}), HashMap.class);
        Long xindayu1ciCount = (Long)xindayu1ciList.get(0).get("count(*)");
        staff.setXinCount(xindayu1ciCount.intValue());

        return null;
    }
}
